{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "\n",
    "sys.path.append(\"..\")  # 也可以这样\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "\n",
    "from db_tools import MysqlTool\n",
    "\n",
    "mysql_db = MysqlTool()\n",
    "# 定义SQL查询\n",
    "query = \"\"\"\n",
    "SELECT user_id, order_no\n",
    "FROM pay\n",
    "WHERE is_renewed = -1 AND status = \"成功\"\n",
    "\"\"\"\n",
    "\n",
    "# 使用pandas读取数据\n",
    "df = pd.read_sql(query, mysql_db.engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "\n",
    "def get_user_pay_count(user_id):\n",
    "    url = f\"https://scapi.tayunapi.com/funcLog/userOrders?user_id={user_id}\"\n",
    "    res = requests.get(url)\n",
    "    return res.json()[\"count\"]\n",
    "\n",
    "\n",
    "# get_user_pay_count(user_id=2159712)\n",
    "df.loc[:1, \"is_renewed\"] = df.loc[:1, \"user_id\"].apply(get_user_pay_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'user_id': 2152257,\n",
       " 'order_no': 'scsq0520240528000235021522572197',\n",
       " 'is_renewed': 1.0}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info = df.iloc[1].to_dict()\n",
    "data_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UPDATE pay\n",
      "SET is_renewed = 1.0\n",
      "WHERE order_no = 'scsq0520240528000235021522572197';\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 续费用户的\n",
    "data_info = df.iloc[1]\n",
    "\n",
    "\n",
    "sql = f\"\"\"UPDATE pay\n",
    "SET is_renewed = {data_info['is_renewed']}\n",
    "WHERE order_no = '{data_info['order_no']}';\"\"\"\n",
    "print(sql)\n",
    "mysql_db.execute_sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT create_time_format FROM pay\n",
    "ORDER BY create_time_format DESC\n",
    "LIMIT 1\"\"\"\n",
    "\n",
    "from pandas import read_sql\n",
    "df_new = read_sql(engin =mysql_db.engine,)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mysql_db.upsert_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "mysql_db.upsert_df(df, table_name=\"type4\", primary_key=\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_list = [\n",
    "    {\n",
    "        \"id\": 1133309,\n",
    "        \"func_code\": \"mine|buy_vip|0|0\",\n",
    "        \"module\": \"mine\",\n",
    "        \"action\": \"buy_vip\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716873744,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1133302,\n",
    "        \"func_code\": \"tab|mine|0|0\",\n",
    "        \"module\": \"tab\",\n",
    "        \"action\": \"mine\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716873743,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1133166,\n",
    "        \"func_code\": \"bookcps|cps_book|17021|0\",\n",
    "        \"module\": \"bookcps\",\n",
    "        \"action\": \"cps_book\",\n",
    "        \"item_id\": 17021,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716873694,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1133148,\n",
    "        \"func_code\": \"tab|bookcps|0|0\",\n",
    "        \"module\": \"tab\",\n",
    "        \"action\": \"bookcps\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716873688,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1133143,\n",
    "        \"func_code\": \"tab|index|0|0\",\n",
    "        \"module\": \"tab\",\n",
    "        \"action\": \"index\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716873687,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1133137,\n",
    "        \"func_code\": \"tab|mine|0|0\",\n",
    "        \"module\": \"tab\",\n",
    "        \"action\": \"mine\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716873686,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1029213,\n",
    "        \"func_code\": \"tab|index|0|0\",\n",
    "        \"module\": \"tab\",\n",
    "        \"action\": \"index\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716820289,\n",
    "    },\n",
    "    {\n",
    "        \"id\": 1029205,\n",
    "        \"func_code\": \"tab|mine|0|0\",\n",
    "        \"module\": \"tab\",\n",
    "        \"action\": \"mine\",\n",
    "        \"item_id\": 0,\n",
    "        \"item_custom\": 0,\n",
    "        \"from\": 4,\n",
    "        \"user_id\": 1838250,\n",
    "        \"click_num\": 1,\n",
    "        \"uuid\": \"26893B2D-DBA6-432D-95BF-9C973156DB5B\",\n",
    "        \"channel\": \"default\",\n",
    "        \"verid\": 156,\n",
    "        \"is_vip\": 0,\n",
    "        \"create_time\": 1716820287,\n",
    "    },\n",
    "]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sucai_bi",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
